
ALTER              FUNCTION fn_shipped_orders_by_month(
  
  @CUSTOMER_INPUT VARCHAR(100),
  @PRODUCT_INPUT VARCHAR(100),
  @MONTH_FROM INT,
  @MONTH_TO INT,
  @YEAR_INPUT INT )
  
  RETURNS @FN_RESULTS TABLE( CUSTOMER VARCHAR(100), PRODUCT VARCHAR(100), MONTH INT, YEAR INT, QUANTITY INT, UNIT_PRICE FLOAT )
  
  AS
  BEGIN
  	DECLARE @OLD_PRICE FLOAT

	DECLARE @TEMP_CURSOR CURSOR 
  
  	DECLARE @YEAR_TO INT
  
  	SET @MONTH_FROM = @MONTH_FROM + 1
  	SET @MONTH_TO = @MONTH_TO + 1
  
  	SET @YEAR_TO = @YEAR_INPUT + @MONTH_TO/12
  	SET @MONTH_TO = @MONTH_TO%12
  
  	IF @CUSTOMER_INPUT IS NULL OR RTRIM( @CUSTOMER_INPUT ) = ''
  	BEGIN
  		SET @CUSTOMER_INPUT = ''
  	END
  	/*
  	SET @TEMP_CURSOR = CURSOR FOR
  	SELECT CUST.C_NAME[CUSTOMER], P.PART_NUMBER[PRODUCT], DATEPART( MONTH, CST.CST_SHIP_DATE )-1, 
  	DATEPART( YEAR, CST.CST_SHIP_DATE ), CSOL.CSOL_QTY[QUANTITY], CSOL.CSOL_UNIT_COST[UNIT_PRICE]
  	FROM CUSTOMER_SHIPPING_TABLE CST, CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT, 
  	CUSTOMER_SHIPPED_ORDER_LINEITEM CSOL, PRODUCT P, SALES_ORDER SO, CUSTOMER CUST 
  	WHERE ( ( CUST.C_NAME = @CUSTOMER_INPUT ) OR ( @CUSTOMER_INPUT = '' ) )
  	AND P.PART_NUMBER = @PRODUCT_INPUT 	
  	AND ( ( DATEPART( MONTH, CST.CST_SHIP_DATE ) >= @MONTH_FROM AND DATEPART( YEAR,  CST.CST_SHIP_DATE ) = @YEAR_INPUT ) OR
  	      ( DATEPART( MONTH, CST.CST_SHIP_DATE ) <= @MONTH_TO AND DATEPART( YEAR,  CST.CST_SHIP_DATE ) = @YEAR_TO AND @YEAR_INPUT <> @YEAR_TO ) OR
  	      ( DATEPART( YEAR,  CST.CST_SHIP_DATE ) < @YEAR_TO AND DATEPART( YEAR,  CST.CST_SHIP_DATE ) > @YEAR_INPUT ) )
  	AND CST.CST_STATUS = 'SHIPPED'
  	AND CST.CUSTOMERSHIPPING_ID = CSLIT.CUSTOMERSHIPPING_ID
  	AND CSLIT.LINEITEM_ID = CSOL.LINEITEM_ID
  	AND CSOL.PRODUCT_ID = P.PRODUCT_ID
  	AND CSOL.SALESORDER_ID = SO.SALESORDER_ID
  	AND SO.CUSTOMER_ID = CUST.CUSTOMER_ID
  	*/
  
 	-- Modified by Gandhi on 29 July 2004
 	-- For all the conditions query has been changed by replacing CST with CSLIT for SHIIPED DATE,SHIPPED QUANTITY AND SHIPPED STATUS
  
  	IF @CUSTOMER_INPUT = '0' AND @PRODUCT_INPUT = '0'
  	BEGIN
  		SET @TEMP_CURSOR = CURSOR FOR
  	    	SELECT CUST.C_NAME[CUSTOMER], P.PART_NUMBER[PRODUCT], DATEPART( MONTH, CSLIT.CSLIT_SHIPPED_DATE )-1, 
  		DATEPART( YEAR, CSLIT.CSLIT_SHIPPED_DATE ),CSLIT.CSLIT_QTY_SHIPPED[QUANTITY], COOL.COOL_UNIT_COST[UNIT_PRICE]
  		FROM CUSTOMER_SHIPPING_TABLE CST, CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT, 
  		CUSTOMER_OPEN_ORDER_LINEITEM COOL, PRODUCT P, SALES_ORDER SO, CUSTOMER CUST  
  		WHERE ( ( DATEPART( MONTH, CSLIT.CSLIT_SHIPPED_DATE ) >= @MONTH_FROM AND DATEPART( YEAR,  CSLIT.CSLIT_SHIPPED_DATE ) = @YEAR_INPUT ) OR
  		      ( DATEPART( MONTH, CSLIT.CSLIT_SHIPPED_DATE ) <= @MONTH_TO AND DATEPART( YEAR,  CSLIT.CSLIT_SHIPPED_DATE ) = @YEAR_TO AND @YEAR_INPUT <> @YEAR_TO ) OR
  		      ( DATEPART( YEAR,  CSLIT.CSLIT_SHIPPED_DATE ) < @YEAR_TO AND DATEPART( YEAR,  CSLIT.CSLIT_SHIPPED_DATE ) > @YEAR_INPUT ) ) 
 				
  		AND CSLIT.CSLIT_STATUS = 'SHIPPED'
 		AND CST.CUSTOMERSHIPPING_ID = CSLIT.CUSTOMERSHIPPING_ID
  		AND CSLIT.LINEITEM_ID = COOL.LINEITEM_ID
  		AND COOL.PRODUCT_ID = P.PRODUCT_ID
  		AND COOL.SALESORDER_ID = SO.SALESORDER_ID
 		AND SO.CUSTOMER_ID = CUST.CUSTOMER_ID
		and SO.SO_Is_Sample = 1
  	END	
  	ELSE IF @CUSTOMER_INPUT = '0' AND @PRODUCT_INPUT <> '0'
  	BEGIN
  		SET @TEMP_CURSOR = CURSOR FOR
  	    	SELECT CUST.C_NAME[CUSTOMER], P.PART_NUMBER[PRODUCT], DATEPART( MONTH, CSLIT.CSLIT_SHIPPED_DATE )-1, 
  		DATEPART( YEAR, CSLIT.CSLIT_SHIPPED_DATE ),CSLIT.CSLIT_QTY_SHIPPED[QUANTITY], COOL.COOL_UNIT_COST[UNIT_PRICE]
  		FROM CUSTOMER_SHIPPING_TABLE CST, CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT, 
  		CUSTOMER_OPEN_ORDER_LINEITEM COOL, PRODUCT P, SALES_ORDER SO, CUSTOMER CUST  
  		WHERE   P.PART_NUMBER = @PRODUCT_INPUT 		
  		AND    ( ( DATEPART( MONTH, CSLIT.CSLIT_SHIPPED_DATE ) >= @MONTH_FROM AND DATEPART( YEAR,  CSLIT.CSLIT_SHIPPED_DATE ) = @YEAR_INPUT ) OR
  		      ( DATEPART( MONTH, CSLIT.CSLIT_SHIPPED_DATE ) <= @MONTH_TO AND DATEPART( YEAR,  CSLIT.CSLIT_SHIPPED_DATE ) = @YEAR_TO AND @YEAR_INPUT <> @YEAR_TO ) OR
  		      ( DATEPART( YEAR,  CSLIT.CSLIT_SHIPPED_DATE ) < @YEAR_TO AND DATEPART( YEAR,  CSLIT.CSLIT_SHIPPED_DATE ) > @YEAR_INPUT ) ) 
 				
  		AND CSLIT.CSLIT_STATUS = 'SHIPPED'
 		AND CST.CUSTOMERSHIPPING_ID = CSLIT.CUSTOMERSHIPPING_ID
  		AND CSLIT.LINEITEM_ID = COOL.LINEITEM_ID
  		AND COOL.PRODUCT_ID = P.PRODUCT_ID
  		AND COOL.SALESORDER_ID = SO.SALESORDER_ID
 		AND SO.CUSTOMER_ID = CUST.CUSTOMER_ID
		and SO.SO_Is_Sample = 1
  	END
  	ELSE IF @CUSTOMER_INPUT <> '0' AND @PRODUCT_INPUT = '0'
  	BEGIN
  		SET @TEMP_CURSOR = CURSOR FOR
  	    	SELECT CUST.C_NAME[CUSTOMER], P.PART_NUMBER[PRODUCT], DATEPART( MONTH, CSLIT.CSLIT_SHIPPED_DATE )-1, 
  		DATEPART( YEAR, CSLIT.CSLIT_SHIPPED_DATE ),CSLIT.CSLIT_QTY_SHIPPED[QUANTITY], COOL.COOL_UNIT_COST[UNIT_PRICE]
  		FROM CUSTOMER_SHIPPING_TABLE CST, CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT, 
  		CUSTOMER_OPEN_ORDER_LINEITEM COOL, PRODUCT P, SALES_ORDER SO, CUSTOMER CUST  
  		WHERE  CUST.C_NAME = @CUSTOMER_INPUT
  	--	AND P.PART_NUMBER = @PRODUCT_INPUT 		
  		AND    ( ( DATEPART( MONTH, CSLIT.CSLIT_SHIPPED_DATE ) >= @MONTH_FROM AND DATEPART( YEAR,  CSLIT.CSLIT_SHIPPED_DATE ) = @YEAR_INPUT ) OR
  		      ( DATEPART( MONTH, CSLIT.CSLIT_SHIPPED_DATE ) <= @MONTH_TO AND DATEPART( YEAR,  CSLIT.CSLIT_SHIPPED_DATE ) = @YEAR_TO AND @YEAR_INPUT <> @YEAR_TO ) OR
  		      ( DATEPART( YEAR,  CSLIT.CSLIT_SHIPPED_DATE ) < @YEAR_TO AND DATEPART( YEAR,  CSLIT.CSLIT_SHIPPED_DATE ) > @YEAR_INPUT ) ) 
 				
  		AND CSLIT.CSLIT_STATUS = 'SHIPPED'
 		AND CST.CUSTOMERSHIPPING_ID = CSLIT.CUSTOMERSHIPPING_ID
  		AND CSLIT.LINEITEM_ID = COOL.LINEITEM_ID
  		AND COOL.PRODUCT_ID = P.PRODUCT_ID
  		AND COOL.SALESORDER_ID = SO.SALESORDER_ID
 		AND SO.CUSTOMER_ID = CUST.CUSTOMER_ID
		and SO.SO_Is_Sample = 1
  	END
  	ELSE IF @CUSTOMER_INPUT <> '0' AND @PRODUCT_INPUT <> '0'
  	BEGIN
  		SET @TEMP_CURSOR = CURSOR FOR
  	    	SELECT CUST.C_NAME[CUSTOMER], P.PART_NUMBER[PRODUCT], DATEPART( MONTH, CSLIT.CSLIT_SHIPPED_DATE )-1, 
  		DATEPART( YEAR, CSLIT.CSLIT_SHIPPED_DATE ),CSLIT.CSLIT_QTY_SHIPPED[QUANTITY], COOL.COOL_UNIT_COST[UNIT_PRICE]
  		FROM CUSTOMER_SHIPPING_TABLE CST, CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT, 
  		CUSTOMER_OPEN_ORDER_LINEITEM COOL, PRODUCT P, SALES_ORDER SO, CUSTOMER CUST  
  		WHERE  CUST.C_NAME = @CUSTOMER_INPUT
  		AND P.PART_NUMBER = @PRODUCT_INPUT 		
  		AND    ( ( DATEPART( MONTH, CSLIT.CSLIT_SHIPPED_DATE ) >= @MONTH_FROM AND DATEPART( YEAR,  CSLIT.CSLIT_SHIPPED_DATE ) = @YEAR_INPUT ) OR
  		      ( DATEPART( MONTH, CSLIT.CSLIT_SHIPPED_DATE ) <= @MONTH_TO AND DATEPART( YEAR,  CSLIT.CSLIT_SHIPPED_DATE ) = @YEAR_TO AND @YEAR_INPUT <> @YEAR_TO ) OR
  		      ( DATEPART( YEAR,  CSLIT.CSLIT_SHIPPED_DATE ) < @YEAR_TO AND DATEPART( YEAR,  CSLIT.CSLIT_SHIPPED_DATE ) > @YEAR_INPUT ) ) 
 				
  		AND CSLIT.CSLIT_STATUS = 'SHIPPED'
 		AND CST.CUSTOMERSHIPPING_ID = CSLIT.CUSTOMERSHIPPING_ID
  		AND CSLIT.LINEITEM_ID = COOL.LINEITEM_ID
  		AND COOL.PRODUCT_ID = P.PRODUCT_ID
  		AND COOL.SALESORDER_ID = SO.SALESORDER_ID
 		AND SO.CUSTOMER_ID = CUST.CUSTOMER_ID
		and SO.SO_Is_Sample = 1
  	END
 	-- Modification ends here - Gandhi 	
 
  	DECLARE @CUSTOMER VARCHAR(100)
  	DECLARE @PRODUCT VARCHAR(100)
  	DECLARE @MONTH INT
  	DECLARE @YEAR INT
  	DECLARE @QUANTITY INT
  	DECLARE @OLD_QUANTITY INT
  	DECLARE @UNIT_PRICE FLOAT
  	DECLARE @ROW_COUNT INT
  	
  	OPEN @TEMP_CURSOR 
  		FETCH NEXT FROM @TEMP_CURSOR
  		INTO @CUSTOMER, @PRODUCT, @MONTH, @YEAR, @QUANTITY, @UNIT_PRICE
  		
  		WHILE @@FETCH_STATUS = 0
  		BEGIN
  			SELECT @ROW_COUNT = COUNT(*) FROM @FN_RESULTS 
  			WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND MONTH = @MONTH AND YEAR = @YEAR
  				
  			IF @ROW_COUNT = 0
  			BEGIN
  				INSERT INTO @FN_RESULTS VALUES ( @CUSTOMER, @PRODUCT, @MONTH, @YEAR, @QUANTITY, @QUANTITY * @UNIT_PRICE)
  			END
  
  			ELSE
  			BEGIN
  				SELECT @OLD_QUANTITY = QUANTITY, @OLD_PRICE = UNIT_PRICE FROM @FN_RESULTS
  				WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND MONTH = @MONTH AND YEAR = @YEAR
  
  				UPDATE @FN_RESULTS SET QUANTITY = (@QUANTITY + @OLD_QUANTITY) , UNIT_PRICE = @OLD_PRICE +  ( @QUANTITY * @UNIT_PRICE )
  				WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND MONTH = @MONTH AND YEAR = @YEAR
  			END	
			  			
  			FETCH NEXT FROM @TEMP_CURSOR
  			INTO  @CUSTOMER, @PRODUCT, @MONTH, @YEAR, @QUANTITY, @UNIT_PRICE
  		END
  
  	CLOSE @TEMP_CURSOR
  	DEALLOCATE @TEMP_CURSOR
   
  	RETURN
  END
 
